<?php
//$Id: Db.php 522 2012-08-15 09:22:00Z tomqin@gmail.com $

/**
 * The Database Class
 *
 * @copyright   Copyright (C) 2010-2012 SupeFun.com Inc.
 * @author      TomQin <tomqin@gmail.com>
 * @license     http://www.supefun.com/license/
 */

class Db{

    const DB_ASSOC = MYSQLI_ASSOC;
    const DB_NUM   = MYSQLI_NUM;
    const DB_BOTH  = MYSQLI_BOTH;

    static protected $table = null;
    static protected $db = null;
    static protected $options = array();

    /**
     * Initialize Db class with options
     *
     * @param array
     * @return void
     */
    static public function initDb(array $opts) {
        self::$options = array();
        self::$options['server']     = 'prod'; // prod|qa|dev|etc
        self::$options['host']       = '127.0.0.1';
        self::$options['username']   = null;
        self::$options['password']   = null;
        self::$options['database']   = null;

        self::$options['port']       = 3306;
        self::$options['sock']       = null; // socket or named pipe
        self::$options['flags']      = null; // null ignore; MYSQLI_CLIENT_COMPRESS, etc
        self::$options['charset']    = null; // null ignore, else utf8, etc
        self::$options['timeout']    = null; // null ignore, else nbr secs

        self::$options = array_merge(self::$options, $opts);
    }

    /**
     * Return the MySQLi object
     */
    static public function getDb(){
        if(!self::$db) {
            //mysqli_report(MYSQLI_REPORT_STRICT);

            $db = new Mysqli;
            $db->init(); // init mysqli for options, connection
            if (!empty(self::$options['timeout'])) {
                $ret = $db->options(MYSQLI_OPT_CONNECT_TIMEOUT, self::$options['timeout']);
            }

            $ret = $db->real_connect(self::$options['host'], self::$options['username'], self::$options['password'],
                              self::$options['database'], self::$options['port'], self::$options['sock'],
                              self::$options['flags']);
            if (!$ret || $db === false || $db->connect_errno) {
                throw new DbException($db->connect_error, $db->connect_errno);
            }
            if (!empty(self::$options['charset'])) {
                $db->set_charset(self::$options['charset']);
            }
            self::$db = $db;
        }
        return self::$db;
    }

    /**
     * Return a special DbTable object
     *
     * @param string
     * @return DbTable
     */
    static public function getTable($name){
        if(!isset(self::$table[$name])) {
            self::$table[$name] = new DbTable($name);
        }
        return self::$table[$name];
    }

    /**
     * Perform a query on the database
     *
     * @param string
     * @return Mysqli_Result
     */
    static public function query($sql) {
        $db = self::getDb();

        $result = $db->query($sql);
        if(!$result){
            if($db->errno){
                throw new DbException($db->error, $db->errno);
            }else{
                return false;
            }
        }
        return $result;
    }

    /**
     * Prepare an SQL statement for execution
     *
     * @param string
     * @return Mysqli_Result
     */
    static public function prepare($sql){
        $db = self::getDb();

        $stmt = $db->prepare($sql);
        if(!$stmt){
            if($db->errno){
                throw new DbException($db->error, $db->errno);
            }else{
                return false;
            }
        }
        return $stmt;
    }

    /**
     * Escape special characters in a string for use in an SQL statement
     *
     * @param string
     * @return string
     */
    static public function escapeString($str){
        if(is_array($str)){
            foreach($str as $k=>$v){
                $str[$k] = self::escapeString($v);
            }
        }else{
            $str = self::getDb()->real_escape_string($str);
        }
        return $str;
    }

    /**
     * Perform a query on the database and get the first field from the result
     *
     * @param string
     * @param array
     * @return string|false
     */
    static public function getFirst($sql, array $bind = array()){
        $db = self::getDb();

        if($bind){
            if($stmt = self::prepare($sql)){
                $tmp = str_repeat('s', count($bind));
                array_unshift($bind, $tmp);
                call_user_func_array(array($stmt, 'bind_param'), $bind);

                $stmt->execute();
                $stmt->store_result();

                $result = self::fetchStmt($stmt, false);
                return current($result);
            }
        }else{
            if($result = self::query($sql)){
                $row = $result->fetch_row();
                return $row[0];
            }
        }
        return false;
    }

    /**
     * Perform a query on the database and get a result row as an array
     *
     * @param string
     * @param array
     * @return string|false
     */
    static public function getRow($sql, array $bind = array()){
        $db = self::getDb();

        if($bind){
            if($stmt = self::prepare($sql)){
                $tmp = str_repeat('s', count($bind));
                array_unshift($bind, $tmp);
                call_user_func_array(array($stmt, 'bind_param'), $bind);

                $stmt->execute();
                $stmt->store_result();

                return self::fetchStmt($stmt, false);
            }
        }else{
            if($result = self::query($sql)){
                return $result->fetch_assoc();
            }
        }
        return false;
    }

    /**
     * Perform a query on the database and fetch all result rows as an array
     *
     * @param string
     * @param array
     * @return string|false
     */
    static public function getAll($sql, array $bind = array(), $resType = self::DB_ASSOC){
        $db = self::getDb();

        if($bind){
            if($stmt = self::prepare($sql)){
                $tmp = str_repeat('s', count($bind));
                array_unshift($bind, $tmp);
                call_user_func_array(array($stmt, 'bind_param'), $bind);

                $stmt->execute();
                $stmt->store_result();

                return self::fetchStmt($stmt, true);
            }
        }else{
            $data = false;
            if($result = self::query($sql)){
                while($row = $result->fetch_assoc()){
                    $data[] = $row;
                }
                return $data;
            }
        }
        return false;
    }

    /**
     * Fetch results from a Mysqli_Stmt object
     *
     * @param Mysqli_Stmt
     * @param bool
     * @return array
     */
    static private function fetchStmt(Mysqli_Stmt $stmt, $all) {
        $result = $out = $fields = array();
        $field  = $stmt->result_metadata()->fetch_fields();

        //get all the field names
        foreach ($field as $val) {
            $fields[] = &$out[$val->name];
        }
        //bind the field name to bind_result method
        call_user_func_array(array($stmt,'bind_result'), $fields);
        if($all){
            while ($stmt->fetch()) {
                $tmp = array();  // one row result array
                foreach ($out as $key => $val) {
                    $tmp[$key] = $val;
                }
                $result[] = $tmp;
            }
            return $result;  //Two dimensional array
        }else{
            foreach ($out as $key => $val) {
                $result[$key] = $val;
            }
            return $result;  //One dimensional array
        }
    }

    /**
     *
     * note: prepare not support mulit query mode (under win32)
     */
    public static function proc($sql, $result_type = MYSQLI_ASSOC) {

        $command = self::getCommand($sql);
        if (!in_array($command, array('CALL'))) {
            throw new Exception('Only CALL allowed via Db::proc().');
        }

        $result = self::$db->multi_query($sql);
        if (!$result && self::$db->errno) {
            throw new Exception('Unable to proc a sql.');
        } else if ($result) {
            $data = array();
            do {
                if ($result = self::$db->store_result()) {
                    $rowset = array();
                    while($row = $result->fetch_assoc()){
                        $rowset[] = $row;
                    }
                    $data[] = $rowset;
                    $result->free();
                }
            } while (self::$db->more_results() && self::$db->next_result());

            return $data;
        } else {
            return ture;
        }
    }

    public static function getCommand($sql) {
        $command = strtoupper(substr($sql, 0, strpos(trim($sql), ' ')));
        return $command;
    }

    public static function getQueries(){
        return self::$db->queries;
    }
}

class DbException extends Exception{}
?>